﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NPSoft.Ferrero.DataBridge.Model;
using NPSoft.Ferrero.Framework.DAL;

namespace NPSoft.Ferrero.DataBridge.DAL
{
    public class Ex_FUtil : IDAL<Ex_F>
    {
        public int Add(Ex_F info)
        {
            throw new NotImplementedException();
        }

        public int Edit(Ex_F info)
        {
            throw new NotImplementedException();
        }

        public Ex_F GetById(long id)
        {
            throw new NotImplementedException();
        }

        public Ex_F GetById(Guid id)
        {
            throw new NotImplementedException();
        }

        public int Remove(Ex_F info)
        {
            throw new NotImplementedException();
        }

        public System.Data.DataSet Search(StringBuilder condtion)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// SQL连接
        /// </summary>
        private SqlConnection Connection
        {
            get
            {
                return SqlHelper.GetConnection();
            }
        }

        public ICollection<Ex_F> SearchCollection(StringBuilder condtion)
        {
            ICollection<Ex_F> infos = new List<Ex_F>();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT  RGN_DESC_CN AS SOffName ,
        RGN_DESC_EN AS SoffEName ,
        City_Chinese_Name_Of_Ship_To AS City,
        CITY_CD AS City_Code,
              Ship_To_Code AS ShipTo,
              DIST_FACT_NAME AS ShipToParty,
              Order_Reason_Code AS OrdRs,
              [77Code] AS Material,
              PRD.PRD_DESC1 AS MaterialENName,
              PRD.PRD_DESC2 AS MaterialCNName,
              CONVERT(VARCHAR(8),Actual_Goods_Issue_Date) AS AcGiDateYearMonth,
              Area_English_Name AS AreaName,
              ST.CITYCLUSTER AS ClusterName,
              SAP_Category AS Category,
              Brand AS DescrizioneSelezione,
              Amount_Of_Order AS Qli,
              Required_Delivery_Date_Qty AS RequiredDelivQty_CT,
              Net_Value AS NetValue
FROM    dbo.EX_F
        LEFT JOIN  dbo.UFN_GET_TREEINFO('ST')  AS ST ON SAP_City = ST.CITY_CD
              LEFT JOIN dbo.MST_ADDRESS AS ADR ON dbo.EX_F.Ship_To_Code = ADR.ADDR_CD
              LEFT JOIN dbo.MST_DIST_FACT AS DIST ON ADR.DIST_CD = DIST.DIST_FACT_CD
              LEFT JOIN dbo.MST_PRD AS PRD ON PRD.PRD_CD = [77Code] ");

            string sWhere = " where ";
            if (!string.IsNullOrEmpty(condtion.ToString()))
            {

                sql.Append(sWhere + condtion);
            }
            DataSet ds;
            ds = SqlHelper.ExecuteDataset(this.Connection, CommandType.Text, sql.ToString());
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                infos.Add(SetInfo(row));
            }
            return infos;
        }

        public Ex_F SetInfo(System.Data.DataRow row)
        {
            Ex_F ex = new Ex_F();
            ex.SOffName = SqlHelper.GetString(row, "SOffName");
            ex.SoffEName = SqlHelper.GetString(row, "SoffEName");
            ex.City = SqlHelper.GetString(row, "City");
            ex.CityCode = SqlHelper.GetString(row, "City_Code");
            ex.ShipTo = SqlHelper.GetString(row, "Ship To");
            ex.ShipToParty = SqlHelper.GetString(row, "Ship ToParty");
            ex.OrdRs = SqlHelper.GetString(row, "OrdRs");
            ex.Material = SqlHelper.GetString(row, "Material");
            ex.MaterialENName = SqlHelper.GetString(row, "MaterialENName");
            ex.MaterialCNName = SqlHelper.GetString(row, "MaterialCNName");
            ex.AcGiDateYearMonth = SqlHelper.GetString(row, "AcGiDateYearMonth");
            ex.AreaName = SqlHelper.GetString(row, "AreaName");
            ex.ClusterName = SqlHelper.GetString(row, "ClusterName");
            ex.Category = SqlHelper.GetString(row, "Category");
            ex.DescrizioneSelezione = SqlHelper.GetString(row, "DescrizioneSelezione");
            ex.Qli = SqlHelper.GetString(row, "Qli");
            ex.RequiredDelivQty_CT = SqlHelper.GetString(row, "RequiredDelivQty_CT");
            ex.NetValue = SqlHelper.GetString(row, "NetValue");


            return ex;
        }
    }
}
